package com.irdstudio.apicenter.console.dm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Component;

import com.irdstudio.apicenter.frame.core.jdbc.util.ConnectionManager;

@Component(value = "appUserDao")
public class AppUserDao {

	public List<AppRole> queryAppRoleListNotYet(String userid, String appid)
			throws SQLException {
		// TODO Auto-generated method stub
		List<AppRole> arList = new ArrayList<AppRole>();
		AppRole dc = null;
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement("select app_id, app_role_id, app_role_name  "
							+ "from app_role t  "
							+ "where t.app_role_id not in  "
							+ "(select app_role_id from app_user_role where app_userid = ?)  and app_id=?");
			ps.setString(1, userid);
			ps.setString(2, appid);
			rs = ps.executeQuery();
			while (rs.next()) {
				dc = new AppRole();
				dc.setAppId(rs.getString("app_id"));
				dc.setAppRoleId(rs.getString("app_role_id"));
				dc.setAppRoleName(rs.getString("app_role_name"));
				arList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryAppTradePrivateWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return arList;
	}

	public List<AppRole> queryAppRoleListAready(String userid, String appid)
			throws SQLException {
		// TODO Auto-generated method stub
		List<AppRole> arList = new ArrayList<AppRole>();
		AppRole dc = null;
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement("select a.app_id, a.app_role_id, b.app_role_name  "
							+ "from app_user_role a, app_role b   "
							+ "where a.app_role_id = b.app_role_id  "
							+ "and a.app_userid = ?  and  a.app_id=?  and  b.app_id=?");
			ps.setString(1, userid);
			ps.setString(2, appid);
			ps.setString(3, appid);
			rs = ps.executeQuery();
			while (rs.next()) {
				dc = new AppRole();
				dc.setAppId(rs.getString("app_id"));
				dc.setAppRoleId(rs.getString("app_role_id"));
				dc.setAppRoleName(rs.getString("app_role_name"));
				arList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryAppTradePrivateWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return arList;
	}

	public List<AppDuty> queryAppDutyListNotYet(String userid, String appid)
			throws SQLException {
		// TODO Auto-generated method stub
		List<AppDuty> dtList = new ArrayList<AppDuty>();
		AppDuty dc = null;
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement(" select app_id, app_duty_id, app_duty_name  "
							+ " from app_duty t  "
							+ " where t.app_duty_id not in  "
							+ "(select app_duty_id from app_user_duty where app_userid = ?)  and  app_id=?");
			ps.setString(1, userid);
			ps.setString(2, appid);
			rs = ps.executeQuery();
			while (rs.next()) {
				dc = new AppDuty();
				dc.setAppId(rs.getString("app_id"));
				dc.setAppDutyId(rs.getString("app_duty_id"));
				dc.setAppDutyName(rs.getString("app_duty_name"));
				dtList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryAppTradePrivateWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return dtList;
	}

	public List<AppDuty> queryAppDutyListAready(String userid, String appid)
			throws SQLException {
		// TODO Auto-generated method stub
		List<AppDuty> dtList = new ArrayList<AppDuty>();
		AppDuty dc = null;
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement("   select a.app_id, a.app_duty_id, b.app_duty_name  "
							+ "from app_user_duty a, app_duty b  "
							+ "where a.app_duty_id = b.app_duty_id  "
							+ "and a.app_userid = ? and  a.app_id=?  and  b.app_id=?");
			ps.setString(1, userid);
			ps.setString(2, appid);
			ps.setString(3, appid);
			rs = ps.executeQuery();
			while (rs.next()) {
				dc = new AppDuty();
				dc.setAppId(rs.getString("app_id"));
				dc.setAppDutyId(rs.getString("app_duty_id"));
				dc.setAppDutyName(rs.getString("app_duty_name"));
				dtList.add(dc);
			}
		} catch (SQLException e) {
			throw new SQLException("queryAppTradePrivateWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return dtList;
	}

	/**
	 * 根据主键查询单条记录
	 * 
	 * @param
	 * @return
	 * @throws SQLException
	 */
	public AppUser queryWithKeys(String appId, String userId)
			throws SQLException {
		AppUser dc = null;
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement("SELECT * FROM app_user WHERE app_id=? and app_userid=?");
			ps.setString(1, appId);
			ps.setString(2, userId);
			rs = ps.executeQuery();
			if (rs.next()) {
				dc = new AppUser();
				dc.setAppId(rs.getString("app_id"));
				dc.setAppUserId(rs.getString("app_userid"));
				dc.setAppUserName(rs.getString("APP_USERNAME"));
				dc.setAppUserPassword(rs.getString("APP_USER_PASSWORD"));
				dc.setMobilePhone(rs.getString("MOBILE_PHONE"));
				dc.setOrgid(rs.getString("ORGID"));
				dc.setUserState(rs.getString("USER_STATE"));
			}
		} catch (SQLException e) {
			throw new SQLException("queryAppTradePrivateWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close2(rs, conn, ps);
		}
		return dc;
	}

	public int saveAppUser(AppUser appUser) throws SQLException {

		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag;
		try {
			String sql = "insert  into  app_user  values(?,?,?,?,?,?,?);";
			ps = conn.prepareStatement(sql);
			ps.setString(1, appUser.getAppId());
			ps.setString(2, appUser.getAppUserId());
			ps.setString(3, appUser.getAppUserName());
			ps.setString(4, appUser.getAppUserPassword());
			ps.setString(5, appUser.getMobilePhone());
			ps.setString(6, appUser.getOrgid());
			ps.setString(7, appUser.getUserState());
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("queryAppFrameStartWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;

	}

	/**
	 * 修改模块参数
	 * 
	 * @param appModuleParam
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int update(AppUser appUser) throws SQLException,
			ClassNotFoundException {
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag;
		try {
			String sql = "update  app_user  set  APP_USERNAME=?,APP_USER_PASSWORD=?,MOBILE_PHONE=?,ORGID=?,USER_STATE=?  where app_id=? and app_userid=?  ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, appUser.getAppUserName());
			ps.setString(2, appUser.getAppUserPassword());
			ps.setString(3, appUser.getMobilePhone());
			ps.setString(4, appUser.getOrgid());
			ps.setString(5, appUser.getUserState());
			ps.setString(6, appUser.getAppId());
			ps.setString(7, appUser.getAppUserId());
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("queryAppFrameStartWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;

	}

	public int insertUserRole(String roleStr, String appid, String userid)
			throws SQLException {
		// TODO Auto-generated method stub
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag = -1;
		String sql = "delete  from app_user_role  where app_id=?  and  app_userid=?";
		String sql1 = "insert into app_user_role  values(?,?,?)";
		String roleids[] = roleStr.split(",");
		try {
			if (roleids.length > 0) {
				ps = conn.prepareStatement(sql);
				ps.setString(1, appid);
				ps.setString(2, userid);
				flag = ps.executeUpdate();

				for (int i = 0; i < roleids.length; i++) {
					ps = conn.prepareStatement(sql1);
					ps.setString(1, appid);
					ps.setString(2, roleids[i]);
					ps.setString(3, userid);
					flag = ps.executeUpdate();
				}

			}
		} catch (SQLException e) {
			throw new SQLException("queryAppFrameStartWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;
	}

	public int insertUserDuty(String dutyStr, String appid, String userid)
			throws SQLException {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
		Connection conn = ConnectionManager.getConnection();
		PreparedStatement ps = null;
		int flag = -1;
		String sql = "delete  from app_user_duty  where app_id=?  and  app_userid=?";
		String sql1 = "insert into app_user_duty  values(?,?,?)";
		String dutyids[] = dutyStr.split(",");
		try {
			if (dutyids.length > 0) {
				ps = conn.prepareStatement(sql);
				ps.setString(1, appid);
				ps.setString(2, userid);
				flag = ps.executeUpdate();

				for (int i = 0; i < dutyids.length; i++) {
					ps = conn.prepareStatement(sql1);
					ps.setString(1, appid);
					ps.setString(2, dutyids[i]);
					ps.setString(3, userid);
					flag = ps.executeUpdate();
				}

			}
		} catch (SQLException e) {
			throw new SQLException("queryAppFrameStartWithKeys is Wrong!"
					+ e.getMessage());
		} finally {
			close1(conn, ps);
		}
		return flag;
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close(ResultSet theRs, Statement theStmt,
			PreparedStatement thePs) {
		try {
			if (theRs != null)
				theRs.close();
			if (theStmt != null)
				theStmt.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close1(Connection conn, PreparedStatement thePs) {
		try {
			if (conn != null)
				conn.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param theRs
	 * @param theStmt
	 * @param thePs
	 */
	protected void close2(ResultSet theRs, Connection conn,
			PreparedStatement thePs) {
		try {
			if (theRs != null)
				theRs.close();
			if (conn != null)
				conn.close();
			if (thePs != null)
				thePs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
